package app.excel.cartoon.index;

import app.esb.common.DBExcutor;
import app.esb.common.NumberTools;
import app.esb.disk.DiskExcutor;
import app.web.spe.ResSPE;
import cn.lily.com.db.container.reader.DataTable;
import cn.lily.com.file.excel.css.ExcelWriterStyle;
import cn.lily.com.file.excel.io.ExcelWriter;
import cn.lily.com.file.excel.spe.IExcelBorder;
import cn.lily.com.file.excel.spe.IExcelColor;
import cn.lily.com.lang.BusinessDate;
import cn.lily.com.lang.number.Arith;

/**
 * Excel首页处理类
 * @author JiaJ
 *
 */
public class ExcelCartoonIndex {
	
	private static ExcelWriterStyle titleStyle;
	private static ExcelWriterStyle contentStyle;
	
	/**
	 * 构造Sheet
	 * @param excelWriter
	 */
	public static void buildSheet(ExcelWriter excelWriter){
		
		int rowIdx = 0;
		
		//样式处理
		titleStyle = getTitleStyle(excelWriter);
		contentStyle = getContentStyle(excelWriter);
		
		//构造Sheet
		newSheet(excelWriter);
		
		//首行
		newTitle(excelWriter,rowIdx++);
			
		//年份统计
		newContent(excelWriter, rowIdx++, "year");
		
		//总数统计
		newContent(excelWriter, rowIdx++, "total");
		
		//详细数统计
		newContent(excelWriter, rowIdx++, "detail");
		
		//体积统计
		newContent(excelWriter, rowIdx++, "size");
		
		//统计时间
		newContent(excelWriter, rowIdx++, "time");
		
		//统计人
		newContent(excelWriter, rowIdx++, "oper");
		
		System.out.println("统计Sheet构造完毕.........");
			
	}

	/**
	 * 获取标题样式
	 * @param excelWriter
	 * @return
	 */
	private static ExcelWriterStyle getTitleStyle(ExcelWriter excelWriter){
		
		ExcelWriterStyle titleStyle = new ExcelWriterStyle(excelWriter);
		
		titleStyle.getCell().setCellAlign("center")
							.setCellVetical("center")
							.setBackGroundColor(IExcelColor.DARK_BLUE)
							.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
							.setBorder("top", IExcelBorder.BORDER_DOUBLE)
							.setBorder("right", IExcelBorder.BORDER_DOUBLE)
							.setBorder("left", IExcelBorder.BORDER_DOUBLE)
							.setBorderColor("bottom", IExcelColor.GREY_25_PERCENT)
							.setBorderColor("top", IExcelColor.GREY_25_PERCENT)
							.setBorderColor("right", IExcelColor.GREY_25_PERCENT)
							.setBorderColor("left", IExcelColor.GREY_25_PERCENT);
		
		titleStyle.getFont().setFontColor(IExcelColor.WHITE)
			                .setFontSize(14)
			                .setFontBorder();
		
		return titleStyle;
	}
	
	/**
	 * 构造Sheet
	 * @param excelWriter
	 */
	private static void newSheet(ExcelWriter excelWriter){
		
		excelWriter.locateSheet(0);
		excelWriter.renameSheet("统计信息【"+BusinessDate.getToday()+"】");
		
	}
	
	/**
	 * 构造标题
	 * @param excelWriter
	 */
	private static void newTitle(ExcelWriter excelWriter,int rowIdx){
		
		excelWriter.locateRow(rowIdx);
		
		//跨列
		excelWriter.setColSpan(0, 2);
	
		//行高列宽
		excelWriter.setRowHeight(30);
		excelWriter.setColWith(0, 100);
		excelWriter.setColWith(1, 400);
		
		excelWriter.setCellValue(0,"动漫统计信息");
		
		excelWriter.setStyle(0,titleStyle);
		excelWriter.setStyle(1,titleStyle);
		
	}
	
	/**
	 * 获取内容样式
	 * @param excelWriter
	 * @return
	 */
	private static ExcelWriterStyle getContentStyle(ExcelWriter excelWriter){
		
		ExcelWriterStyle contentStyle = new ExcelWriterStyle(excelWriter);
		
		contentStyle.getCell().setCellAlign("center")
								.setCellVetical("center")
								.setBackGroundColor(IExcelColor.GREY_40_PERCENT)
								.setBorder("bottom", IExcelBorder.BORDER_DOUBLE)
								.setBorder("top", IExcelBorder.BORDER_DOUBLE)
								.setBorder("right", IExcelBorder.BORDER_DOUBLE)
								.setBorder("left", IExcelBorder.BORDER_DOUBLE)
								.setBorderColor("bottom", IExcelColor.GREY_25_PERCENT)
								.setBorderColor("top", IExcelColor.GREY_25_PERCENT)
								.setBorderColor("right", IExcelColor.GREY_25_PERCENT)
								.setBorderColor("left", IExcelColor.GREY_25_PERCENT);
		
		contentStyle.getFont().setFontColor(IExcelColor.WHITE)
			                  .setFontSize(10)
			                  .setFontBorder();
		
		return contentStyle;
	}
	
	/**
	 * 构造内容
	 * @param excelWriter
	 * @param rowIdx
	 * @param contentTyp
	 */
	private static void newContent(ExcelWriter excelWriter, int rowIdx , String contentTyp){
		
		excelWriter.locateRow(rowIdx);
		excelWriter.setRowHeight(20);
		
		if(contentTyp.equals("year")){
			newReportYear(excelWriter);
		}
		
		else if(contentTyp.equals("total")){
			newReportTotal(excelWriter);
		}
		
		else if(contentTyp.equals("detail")){
			newReportDetail(excelWriter);		
		}
				
		else if(contentTyp.equals("size")){
			newReportSize(excelWriter);
		}
		
		else if(contentTyp.equals("oper")){
			newReportOper(excelWriter);
		}
		
		else if(contentTyp.equals("time")){
			newReportTimestamp(excelWriter);
		}
		
		excelWriter.setStyle(0,contentStyle);
		excelWriter.setStyle(1,contentStyle);
	}

	/**
	 * 年份信息统计
	 * @param excelWriter
	 * @param rowIdx
	 */
	private static void newReportYear(ExcelWriter excelWriter) {
		
		//放入数据
		String sql = "select max(CAN_TIME) as TIME_MAX,min(CAN_TIME) as TIME_MIN from LEO_CAN_DETAIL_INFO ";
		DataTable dt = DBExcutor.getDBManager().executeQueryTable(sql);
		if(dt.next()){
			excelWriter.setCellValue(0,"时间范围");
			excelWriter.setCellValue(1,dt.getString("TIME_MIN").substring(0,4)+" — "+ dt.getString("TIME_MAX").substring(0,4));
		}
	}
	
	/**
	 * 总数统计
	 * @param excelWriter
	 * @param i
	 */
	private static void newReportTotal(ExcelWriter excelWriter) {
		
		String sql = "select count(*) as NUM_TOTAL from LEO_CAN_INFO ";
		DataTable dt = DBExcutor.getDBManager().executeQueryTable(sql);
		if(dt.next()){
			excelWriter.setCellValue(0,"统计总数");
			excelWriter.setCellValue(1,dt.getString("NUM_TOTAL"));
		}
		
	}
	
	/**
	 * 详细总数统计
	 * @param excelWriter
	 * @param rowIdx
	 */
	private static void newReportDetail(ExcelWriter excelWriter) {
		
		String sql = "select count(*) as NUM_TOTAL from LEO_CAN_DETAIL_INFO ";
		DataTable dt = DBExcutor.getDBManager().executeQueryTable(sql);
		if(dt.next()){
			excelWriter.setCellValue(0,"统计详细数");
			excelWriter.setCellValue(1,dt.getString("NUM_TOTAL"));
		}
		
	}
	
	/**
	 * 统计时间
	 * @param excelWriter
	 * @param rowIdx
	 */
	private static void newReportTimestamp(ExcelWriter excelWriter) {
			
		excelWriter.setCellValue(0,"统计时间");
		excelWriter.setCellValue(1,BusinessDate.getTodaytime());	
		
	}
	
	/**
	 * 统计人
	 * @param excelWriter
	 * @param rowIdx
	 */
	private static void newReportOper(ExcelWriter excelWriter) {
				
		excelWriter.setCellValue(0,"统计人");
		excelWriter.setCellValue(1,"张家骏");
			
	}
	
	/**
	 * 统计体积
	 * @param excelWriter
	 */
	private static void newReportSize(ExcelWriter excelWriter) {
		
		 DataTable resTable = DBExcutor.getDBManager().executeQueryTable("select * from LEO_RES_INFO where RES_TYP='"+ResSPE.DATA_TYP_CARTOON+"'");
		 
		 double sizeTotal = 0.0;
		 
		 while(resTable.next()){
			 
			 sizeTotal = Arith.add(sizeTotal,NumberTools.toKBSize(resTable.getString("RES_STORE_SIZE")));
			 
		 }
		 
		 excelWriter.setCellValue(0,"统计体积");
		 excelWriter.setCellValue(1,NumberTools.toSizeString(sizeTotal));
		
	}



}
